package PrjDBUtils

import (
	"database/sql"
	"dgo/goutils/db-utils"
	"dgo/goutils/utils"
	"errors"

	"fmt"
	"time"

	"strings"
)

var (
	DbBlocker             *utils.BlockCounter                        = utils.NewBlockCounter(5)
	OnDBConfigChangedFunc func(datasourceid string, changedtype int) = nil
)

const (
	check_table_exists        string = "select 1 from %s where 1=0"
	DBConfigChangeType_DELETE int    = 3
	DBConfigChangeType_ADD    int    = 2
	DBConfigChangeType_UPDATE int    = 1
)

func RemoveDatasource(id string) bool {
	return db_utils.RemoveDatasource(id)
}

func ConfigDatasourceAll(id string, connstring string, drivername string, dbtype, fieldprefix, fieldsuffix string) bool {
	chagnedtype := -1

	if len(connstring) == 0 {
		RemoveDatasource(id)
		chagnedtype = DBConfigChangeType_DELETE
	} else {
		if len(dbtype) == 0 {
			dbtype = drivername
		}
		db_utils.ConfigDatasource(id, connstring, drivername, dbtype, fieldprefix, fieldsuffix)
		chagnedtype = DBConfigChangeType_UPDATE
	}

	if chagnedtype != -1 {
		if OnDBConfigChangedFunc != nil {
			OnDBConfigChangedFunc(id, chagnedtype)
		}
		return true
	}
	return false
}

func ConfigDatasource(id string, connstring string, drivername string, dbtype string) bool {
	return ConfigDatasourceAll(id, connstring, drivername, dbtype, "", "")
}

func GetConnString(id string) string {
	return db_utils.GetDatasourceConnstring(id)
}

func GetDBDriverName(id string) string {
	return db_utils.GetDatasourceDriverName(id)
}

func GetDBFromConnStr(driverName string, connstring string) (*sql.DB, error) {
	db, err := sql.Open(driverName, connstring)
	return db, err
}

func GetDBNameFromConnString(connstring string) string {
	// admin:Admin@gnss12@tcp(192.168.120.71:3306)/xcors?
	i1 := strings.Index(connstring, "/")
	i2 := strings.Index(connstring, "?")
	if i1 == -1 || i2 == -1 {
		return ""
	}

	ds_dbname := connstring[i1+1 : i2]
	return ds_dbname
}

func DatabaseExists(db *sql.DB, dbname string) (bool, error) {
	lvsql := fmt.Sprintf("show databases like '%s'", dbname)
	rows, err := db.Query(lvsql)
	if err != nil {
		return false, err
	}

	defer rows.Close()

	return rows.Next(), nil
}

func CheckCreateMySqlDB(datasourceid string, new_db_name string) (int, error) {
	connstr := GetConnString(datasourceid)

	dbName := "$dbname$"
	if !strings.Contains(connstr, dbName) {
		dbName = GetDBNameFromConnString(connstr)
		if len(dbName) == 0 {
			return -1, errors.New("无法获取数据库名称, 连接串:" + connstr)
		}
	}

	connstr = strings.Replace(connstr, dbName, "mysql", 1)
	db, err := GetDBFromConnStr("mysql", connstr)
	if err != nil {
		return -1, err
	}
	defer db.Close()

	ret, err := DatabaseExists(db, new_db_name)
	if err != nil {
		return -1, err
	}

	if !ret {
		lvsql := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS `%s` default charset utf8 COLLATE utf8_general_ci;", new_db_name)
		_, err = ExecSql(db, lvsql)
		if err != nil {

			return -1, err
		}
		return 1, nil
	}

	return 0, nil

}

func CheckCreateMySqlDBNameIfNotExistEx(id string) (int, error) {
	connstr := GetConnString(id)
	dbName := GetDBNameFromConnString(connstr)
	if len(dbName) == 0 {
		return -1, errors.New("无法获取数据库名称, 连接串:" + connstr)
	}

	connstr = strings.Replace(connstr, dbName, "mysql", 1)
	db, err := GetDBFromConnStr("mysql", connstr)
	if err != nil {
		return -1, err
	}
	defer db.Close()

	ret, err := DatabaseExists(db, dbName)
	if err != nil {
		return -1, err
	}

	if !ret {
		lvsql := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS `%s` default charset utf8 COLLATE utf8_general_ci;", dbName)
		//golog.Debugf("create sql:%s", lvsql)
		_, err = ExecSql(db, lvsql)
		if err != nil {

			return -1, err
		}
		return 1, nil
	}

	return 0, nil
}

func CheckCreateMySqlDBNameIfNotExist(id string) error {
	drivername := db_utils.GetDatasourceDriverName(id)

	if drivername == "mysql" {
		connstr := db_utils.GetDatasourceConnstring(id)
		dbName := GetDBNameFromConnString(connstr)
		if len(dbName) == 0 {
			return errors.New("无法获取数据库名称, 连接串:" + connstr)
		}

		connstr = strings.Replace(connstr, dbName, "mysql", 1)
		db, err := GetDBFromConnStr("mysql", connstr)
		if err != nil {
			return err
		}
		defer db.Close()

		ret, err := DatabaseExists(db, dbName)
		if err != nil {
			return err
		}

		if !ret {
			lvsql := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS `%s` default charset utf8 COLLATE utf8_general_ci;", dbName)
			//golog.Debugf("create sql:%s", lvsql)
			_, err = ExecSql(db, lvsql)
			if err != nil {

				return err
			}
		}
	}

	return nil
}

func ExecuteNextSQLExByDB(db *sql.DB,
	getsql func(idx int, preRowsAffected int64, preSelData interface{}) (sqltype int, sql string)) (int64, string, error) {
	idx := 0
	var preRowsAffected int64 = 0
	var preSelData interface{} = nil
	var err error = nil
	for {
		sqltype, lvsql := getsql(idx, preRowsAffected, preSelData)
		if len(lvsql) == 0 {
			break
		}
		if sqltype == 0 {
			preRowsAffected, err = ExecSql(db, lvsql)
		} else {
			rows, err1 := GetRowList(db, lvsql)
			preRowsAffected = int64(len(rows))
			err = err1
			preSelData = &rows
		}

		if err != nil {
			return -1, lvsql, err
		}
		idx++
	}
	return preRowsAffected, "", nil
}

func ExecuteUpOrInsertByDBId(dbid string, recUp *db_utils.RecUpdateUpbuilder, keyfields, tablename string) (int64, string, error) {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return 0, "", err
	}
	return ExecuteUpOrInsertByDB(db, recUp, keyfields, tablename)
}

func ExecuteUpOrInsertByDB(db *sql.DB, recUp *db_utils.RecUpdateUpbuilder, keyfields, tablename string) (int64, string, error) {
	return ExecuteNextSQLExByDB(db, func(idx int, preRowsAffected int64, preSelData interface{}) (sqltype int, sql string) {
		if idx == 0 {
			return 1, fmt.Sprintf("select 1 as id from %s where %s", tablename,
				recUp.BuildWhere(keyfields))
		} else if idx == 1 {
			if preRowsAffected == 0 {
				return 0, recUp.BuildInsert(tablename)
			} else {
				return 0, recUp.BuildUpdate(tablename, keyfields)
			}
		} else {
			return 0, ""
		}
	})
}

func ExecuteNextSQLEx(id string,
	getsql func(idx int, preRowsAffected int64, preSelData interface{}) (sqltype int, sql string)) (int64, string, error) {
	db, err := db_utils.OpenDBConn(id)
	if err != nil {
		return -1, "", err
	}
	return ExecuteNextSQLExByDB(db, getsql)

}

func ExecuteNextSQL(id string, getsql func(idx int, preRowsAffected int64) string) (int64, error) {
	db, err := db_utils.OpenDBConn(id)
	if err != nil {
		return -1, err
	}
	idx := 0
	var preRowsAffected int64 = 0
	for {
		lvsql := getsql(idx, preRowsAffected)
		if len(lvsql) == 0 {
			break
		}
		preRowsAffected, err = ExecSql(db, lvsql)
		if err != nil {
			return -1, err
		}
		idx++
	}

	return preRowsAffected, nil

}

func ExecuteNextSqlIfRowsAffectedIsZero(db *sql.DB, sqls ...string) (int64, string, error) {
	for _, v := range sqls {
		if len(v) == 0 {
			break
		}
		r1, err := ExecSql(db, v)
		if err != nil {
			return r1, v, err
		} else if r1 > 0 {
			return r1, v, nil
		}
	}
	return 0, "", nil
}

func ExecuteSqlOnSelSql(id string, sel_sql, eq_0_sql, gt_0_sql string) (string, int64, error) {
	db, err := db_utils.OpenDBConn(id)
	if err != nil {
		return "", -1, err
	}

	tx, err := db.Begin()
	if err != nil {
		return "", -1, err
	}

	var retErr error
	defer func() {
		if tx != nil {
			if retErr == nil {
				tx.Commit()
			} else {
				tx.Rollback()
			}
		}
	}()

	r1, err := GetRowCountByTx(tx, sel_sql)
	if err != nil {
		retErr = err
		return sel_sql, -1, retErr
	}

	if r1 == 0 {
		r1, err := ExecTxSql(tx, eq_0_sql)
		retErr = err
		return eq_0_sql, r1, retErr
	} else {
		r1, err := ExecTxSql(tx, gt_0_sql)
		retErr = err
		return gt_0_sql, r1, retErr
	}
}

func ExecuteBatchSQLByDB(db *sql.DB, sqls ...string) (int64, string, error) {
	tx, err := db.Begin()
	if err != nil {
		return -1, "", err
	}

	var retErr error
	var errsql string

	defer func() {
		if tx != nil {
			if retErr == nil {
				tx.Commit()
			} else {
				tx.Rollback()
			}
		}
	}()

	var r int64 = 0

	for _, v := range sqls {
		v = utils.Trim(v)
		if len(v) > 0 {
			r1, err := ExecTxSql(tx, v)
			if err != nil {
				retErr = err
				errsql = v
				break
			} else {
				r += r1
			}
		}
	}

	if retErr != nil {
		return -1, errsql, retErr
	}
	return r, "", nil
}

func ExecuteBatchSQL(id string, sqls ...string) (int64, string, error) {
	db, err := db_utils.OpenDBConn(id)
	if err != nil {
		return -1, "", err
	}

	return ExecuteBatchSQLByDB(db, sqls...)

}

// 尽量使用GetDBConn
//func GetDB(id string) (*sql.DB, error) {
//	locker.RLock()
//	itm := configMap[id]
//	locker.RUnlock()
//
//	if itm == nil || len(itm.connectString) == 0 {
//		return nil, errors.New(fmt.Sprintf("%s对应的配置不完整, 连接字符串为空", id))
//	}
//	db, err := sql.Open(itm.driverName, itm.connectString)
//	return db, err
//}

func GetDBConfigFieldPrefixAndSuffix(id string) (string, string) {
	return db_utils.GetDatasourceFieldPrefixAndSuffix(id)
}

func GetDBConnVarDBName(connid, datasourceid, dbname string) (*db_utils.DBConn, error) {
	db, changeDNA, err := db_utils.OpenDBConnExWithChangeDNA(connid, datasourceid, dbname)
	if err != nil {
		return nil, err
	}

	dbconn := db_utils.NewDBConnWithDriverName(db, db_utils.GetDatasourceDriverName(datasourceid), db_utils.GetDatasourceDBType(datasourceid))
	dbconn.CloseDBFlag = 0
	dbconn.ConfigChangeDNA(changeDNA)
	return dbconn, nil
}

func GetDBConn(id string) (*db_utils.DBConn, error) {
	db, err := db_utils.OpenDBConn(id)
	if err != nil {
		return nil, err
	}

	dbconn := db_utils.NewDBConnWithDriverName(db, db_utils.GetDatasourceDriverName(id), db_utils.GetDatasourceDBType(id))
	dbconn.CloseDBFlag = 0
	return dbconn, nil
}

func RecExists(db *sql.DB, table, keyfieldname string, val interface{}, condi string) (bool, error) {
	lvsql := fmt.Sprintf("select 1 from %s where %s=?", table, keyfieldname)
	if len(condi) > 0 {
		lvsql += condi
	}

	rows, err := db.Query(lvsql, val)
	if err != nil {
		return false, err
	}
	defer rows.Close()

	return rows != nil && rows.Next(), nil

}

func GetFirstVal(db *sql.DB, sql string, args ...interface{}) (*db_utils.SqlValue, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		return &rowValues[0], err

	}

	return nil, nil
}

func GetRowCount(db *sql.DB, sql string, args ...interface{}) (int, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	r := 0
	if rows.Next() {
		r++
	}

	return r, nil
}

func GetRowCountByTx(tx *sql.Tx, sql string, args ...interface{}) (int64, error) {
	rows, err := tx.Query(sql, args...)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	r := int64(0)
	if rows.Next() {
		r++
	}

	return r, nil
}

func ForeachRows(rows [][]db_utils.SqlValue, rowEach func(row []db_utils.SqlValue, idx int) bool) {
	for k, v := range rows {
		if !rowEach(v, k) {
			break
		}
	}
}

func ExtractSQLValue(realV interface{}) interface{} {
	switch s := realV.(type) {
	case []byte:
		return string(s)
	case time.Time:
		return s
	case int:
	case int32:
	case int8:
	case int64:
		return s
	case bool:
		return s
	case nil:
		return s
	default:
		return utils.GetStrValue(s, "")
	}

	return realV
}

func GetFirstRowWithColumns(db *sql.DB, sql string, args ...interface{}) ([]db_utils.SqlValue, []string, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return nil, nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		columns, _ := rows.Columns()
		return rowValues, columns, err
	}
	return nil, nil, nil
}

func QryFirstRow(dbid string, sql string, args ...interface{}) ([]db_utils.SqlValue, error) {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return nil, err
	}
	return GetFirstRow(db, sql, args...)
}

func QryFirstVal(dbid string, sql string, args ...interface{}) (*db_utils.SqlValue, error) {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return nil, err
	}

	return GetFirstVal(db, sql, args...)
}

func GetFirstRow(db *sql.DB, sql string, args ...interface{}) ([]db_utils.SqlValue, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	if rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		return rowValues, err

	}

	return nil, nil
}

func GetRowListWithColumns(db *sql.DB, sql string, args ...interface{}) ([][]db_utils.SqlValue, []string, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return nil, nil, err
	}

	defer rows.Close()

	rval := make([][]db_utils.SqlValue, 0, 1024)
	columns, _ := rows.Columns()
	for rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		if err != nil {
			return rval, columns, err
		}
		rval = append(rval, rowValues)
	}

	return rval, columns, nil
}

func QrySQL(dbid string, sql string) ([][]db_utils.SqlValue, error) {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return nil, err
	}
	rows, err := GetRowList(db, sql)
	return rows, err
}

func GetRowList(db *sql.DB, sql string, args ...interface{}) ([][]db_utils.SqlValue, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	rval := make([][]db_utils.SqlValue, 0, 1024)

	for rows.Next() {
		rowValues, err := db_utils.GetRowValues(rows)
		if err != nil {
			return rval, err
		}
		rval = append(rval, rowValues)
	}

	return rval, nil
}

func ExecSQLList(db *sql.DB, sqls []string) (ret int64, lasterr error) {
	ret = 0
	for i := 0; i < len(sqls); i++ {
		asql := utils.Trim(sqls[i])
		if len(asql) > 0 {
			r, err := ExecSql(db, sqls[i])
			if err != nil {
				lasterr = err
				return
			} else {
				ret += r
			}
		}
	}
	return
}

func ExecSql(db *sql.DB, sql string, args ...interface{}) (int64, error) {

	res, err := db.Exec(sql, args...)
	if err != nil {
		return 0, err
	} else {
		affect, err := res.RowsAffected()
		return affect, err
	}
}

func ExecTxSql(tx *sql.Tx, sql string, args ...interface{}) (int64, error) {
	res, err := tx.Exec(sql, args...)
	if err != nil {
		return 0, err
	} else {
		affect, err := res.RowsAffected()
		return affect, err
	}
}

/*
**

	 1: 表存在
	 0: 表不存在
	-1: 出现异常
*/
func CheckTableIsExists(db *sql.DB, tableName string) (int, error) {
	lvsql := fmt.Sprintf(check_table_exists, tableName)
	_, err := ExecSql(db, lvsql)
	if err != nil {
		return 0, err
	} else {
		return 1, nil
	}
}

func CheckTableIsExistsByID(dbid string, tableName string) (int, error) {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return -1, err
	}
	return CheckTableIsExists(db, tableName)
}

func CheckCreateTableByID(dbid string, tableName string, scriptfile string) error {
	db, err := db_utils.OpenDBConn(dbid)
	if err != nil {
		return err
	}
	return CheckCreateTable(db, tableName, scriptfile)
}

func CheckCreateTable(db *sql.DB, tableName string, scriptfile string) error {
	ret, _ := CheckTableIsExists(db, tableName)
	if ret == 0 {
		var lvsql string
		if utils.FileIsExists(scriptfile) {
			strBuf, err := utils.ReadFile(scriptfile)
			if err != nil {
				return errors.New(fmt.Sprintf("读取脚本文件失败:%s", err.Error()))
			}
			lvsql = string(strBuf)
		} else {
			lvsql = scriptfile
		}

		lvsql = strings.Replace(lvsql, "$rep_table$", tableName, -1)
		sqlstrs := strings.Split(lvsql, ";")
		_, errsql, err1 := ExecuteBatchSQLByDB(db, sqlstrs...)
		if err1 != nil {
			return errors.New(fmt.Sprintf("执行创建表语句:%s\n出现异常:%s", errsql, err1.Error()))
		}
		return nil
	}
	return nil
}
